package com.example.mydemo.util;

import java.sql.*;
import java.time.LocalDate;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Random;

public class EmployeeDataInserter {
//    url: jdbc:mysql://localhost:3306/my_spring_boot_db?useSSL=false&serverTimezone=UTC
//    username: root
//    password: 123456
    // 数据库连接配置
    private static final String DB_URL = "jdbc:mysql://localhost:3306/my_spring_boot_db?useSSL=false&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "root";

    // 雪花算法实现
    private static class SnowflakeIdGenerator {
        private static final long START_TIMESTAMP = 1609459200000L; // 2021-01-01
        private static final long WORKER_ID_BITS = 5L;
        private static final long DATACENTER_ID_BITS = 5L;
        private static final long SEQUENCE_BITS = 12L;

        private static final long MAX_WORKER_ID = ~(-1L << WORKER_ID_BITS);
        private static final long MAX_DATACENTER_ID = ~(-1L << DATACENTER_ID_BITS);
        private static final long MAX_SEQUENCE = ~(-1L << SEQUENCE_BITS);

        private static final long WORKER_SHIFT = SEQUENCE_BITS;
        private static final long DATACENTER_SHIFT = SEQUENCE_BITS + WORKER_ID_BITS;
        private static final long TIMESTAMP_SHIFT = SEQUENCE_BITS + WORKER_ID_BITS + DATACENTER_ID_BITS;

        private final long workerId;
        private final long datacenterId;
        private long sequence = 0L;
        private long lastTimestamp = -1L;

        public SnowflakeIdGenerator(long workerId, long datacenterId) {
            if (workerId > MAX_WORKER_ID || workerId < 0) {
                throw new IllegalArgumentException("Worker ID out of range");
            }
            if (datacenterId > MAX_DATACENTER_ID || datacenterId < 0) {
                throw new IllegalArgumentException("Datacenter ID out of range");
            }
            this.workerId = workerId;
            this.datacenterId = datacenterId;
        }

        public synchronized String nextId() {
            long timestamp = System.currentTimeMillis();

            if (timestamp < lastTimestamp) {
                throw new RuntimeException("Clock moved backwards");
            }

            if (timestamp == lastTimestamp) {
                sequence = (sequence + 1) & MAX_SEQUENCE;
                if (sequence == 0) {
                    timestamp = tilNextMillis(lastTimestamp);
                }
            } else {
                sequence = 0L;
            }

            lastTimestamp = timestamp;

            long id = ((timestamp - START_TIMESTAMP) << TIMESTAMP_SHIFT)
                    | (datacenterId << DATACENTER_SHIFT)
                    | (workerId << WORKER_SHIFT)
                    | sequence;

            return String.valueOf(id);
        }

        private long tilNextMillis(long lastTimestamp) {
            long timestamp = System.currentTimeMillis();
            while (timestamp <= lastTimestamp) {
                timestamp = System.currentTimeMillis();
            }
            return timestamp;
        }
    }

    public static void main(String[] args) {
        // 初始化雪花ID生成器（工作ID=1，数据中心ID=1）
        SnowflakeIdGenerator idGenerator = new SnowflakeIdGenerator(1, 1);

        // 姓氏和名字库
        String[] lastNames = {"赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈",
                "褚", "卫", "蒋", "沈", "韩", "杨", "朱", "秦", "尤", "许"};
        String[] firstNames = {"伟", "芳", "娜", "秀英", "敏", "静", "丽", "强", "磊", "军",
                "洋", "勇", "艳", "杰", "娟", "涛", "明", "超", "秀兰", "霞",
                "平", "刚", "桂英", "文", "辉", "丹", "健", "德", "红", "志强"};

        // 职位列表
        String[] jobTitles = {"软件工程师", "产品经理", "UI设计师", "测试工程师", "运维工程师",
                "数据分析师", "项目经理", "销售代表", "人力资源专员", "财务主管",
                "市场专员", "技术总监", "客户经理", "行政助理", "内容运营"};

        // 部门ID范围
        int minDept = 1;
        int maxDept = 5;

        // 状态及其权重
        String[] statusOptions = {"在职", "退休", "辞退"};
        double[] statusWeights = {0.8, 0.15, 0.05}; // 80%在职, 15%退休, 5%辞退

        // 时间格式
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // 1. 加载驱动并建立连接
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

            // 2. 准备SQL插入语句（包含employee_id字段）
            String sql = "INSERT INTO c_employee (employee_id, name, age, gender, department_id, job_title, hire_date, status, create_time, update_time) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            // 3. 生成1000条记录并批量插入
            Random random = new Random();
            LocalDate now = LocalDate.now();

            // 开始事务
            conn.setAutoCommit(false);

            for (int i = 0; i < 5000; i++) {
                // 生成雪花算法ID作为员工编号
                String employeeId = idGenerator.nextId();

                // 生成姓名
                String lastName = lastNames[random.nextInt(lastNames.length)];
                String firstName = firstNames[random.nextInt(firstNames.length)];
                String name = lastName + firstName;

                // 生成性别和年龄
                String gender = random.nextBoolean() ? "男" : "女";
                int age;
                if ("男".equals(gender)) {
                    age = 22 + random.nextInt(38); // 22-59岁
                } else {
                    age = 22 + random.nextInt(33); // 22-54岁
                }

                // 生成部门ID
                int departmentId = minDept + random.nextInt(maxDept - minDept + 1);

                // 生成职位
                String jobTitle = jobTitles[random.nextInt(jobTitles.length)];

                // 生成入职日期（5年前左右 ±1年）
                int yearsAgo = 5 + (random.nextBoolean() ? 1 : -1) * random.nextInt(2);
                int daysOffset = random.nextInt(365 * 2) - 365; // ±1年内的随机天数
                LocalDate hireDate = now.minusYears(yearsAgo).plusDays(daysOffset);

                // 根据年龄和权重生成状态
                String status = statusOptions[0]; // 默认为在职
                double rand = random.nextDouble();
                double cumulativeWeight = 0.0;

                // 调整权重：年龄大于55岁增加退休概率
                double[] adjustedWeights = statusWeights.clone();
                if (age > 55) {
                    adjustedWeights[1] = 0.6; // 退休概率提高到60%
                    adjustedWeights[0] = 0.35; // 在职降低到35%
                    adjustedWeights[2] = 0.05; // 辞退保持5%
                }

                for (int j = 0; j < statusOptions.length; j++) {
                    cumulativeWeight += adjustedWeights[j];
                    if (rand < cumulativeWeight) {
                        status = statusOptions[j];
                        break;
                    }
                }

                // 当前时间作为create_time和update_time
                String currentTime = ZonedDateTime.now().format(dtf);

                // 设置参数
                pstmt.setString(1, employeeId);  // employee_id
                pstmt.setString(2, name);        // name
                pstmt.setInt(3, age);            // age
                pstmt.setString(4, gender);      // gender
                pstmt.setInt(5, departmentId);   // department_id
                pstmt.setString(6, jobTitle);    // job_title
                pstmt.setDate(7, Date.valueOf(hireDate)); // hire_date
                pstmt.setString(8, status);      // status
                pstmt.setString(9, currentTime); // create_time
                pstmt.setString(10, currentTime); // update_time

                // 添加到批处理
                pstmt.addBatch();

                // 每100条执行一次批处理
                if (i % 100 == 0) {
                    pstmt.executeBatch();
                    conn.commit();
                }
            }

            // 执行剩余的批处理
            pstmt.executeBatch();
            conn.commit();

            System.out.println("成功插入1000条员工记录！");

        } catch (ClassNotFoundException e) {
            System.err.println("找不到MySQL驱动: " + e.getMessage());
        } catch (SQLException e) {
            System.err.println("数据库操作错误: " + e.getMessage());
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                System.err.println("回滚失败: " + ex.getMessage());
            }
        } finally {
            // 清理资源
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                System.err.println("关闭连接时出错: " + e.getMessage());
            }
        }
    }
}